﻿using System;
using System.Data.OleDb;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using System.IO;
using System.Runtime.InteropServices;


namespace BAC.Excel.Engine
{
    public class ExcelHelper
    {
        public ExcelHelper()
        {

            ExcelConstants ex = new ExcelConstants();
        }
        public static System.Data.DataTable GetDataFromExcel(string filePath)
        {
            // Create connection string variable. Modify the "Data Source"
            // parameter as appropriate for your environment.
            String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Macro;HDR=YES';";
            // Create connection object by using the preceding connection string.
            OleDbConnection objConn = new OleDbConnection(sConnectionString);

            // Open connection with the database.
            objConn.Open();

            // The code to follow uses a SQL SELECT command to display the data from the worksheet.

            // Create new OleDbCommand to return data from worksheet.
            OleDbCommand objCmdSelect = new OleDbCommand(
    @"SELECT [" + ExcelConstants.NameHeader + "],	[" + ExcelConstants.DisplayNameHeader + "],	[" + ExcelConstants.RequiredLevelHeader + "]	,[" + ExcelConstants.AttributeTypeHeader + "]	,[" + ExcelConstants.DescriptionHeader + "]	,[" + ExcelConstants.AuditingHeader + "],	[" + ExcelConstants.FieldSecurityHeader + "],	[" + ExcelConstants.DefaultValueHeader + "],	[" + ExcelConstants.FormatHeader + "],	[" + ExcelConstants.MaximumLengthHeader + "],	[" + ExcelConstants.MaximumValueHeader + "],	[" + ExcelConstants.MinimumValueHeader + "],	[" + ExcelConstants.PrecisionHeader + "],	[" + ExcelConstants.IMEModeHeader + "],	[" + ExcelConstants.OptionsHeader + "] FROM [CustomEntity$A1:Q4999] where [" + ExcelConstants.NameHeader + "] is not NULL", objConn);
            //A1:Q4999
            // Create new OleDbDataAdapter that is used to build a DataSet
            // based on the preceding SQL SELECT statement.
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

            // Pass the Select command to the adapter.
            objAdapter1.SelectCommand = objCmdSelect;

            // Create new DataSet to hold information from the worksheet.
            System.Data.DataTable dt = new System.Data.DataTable("TableData");
            objAdapter1.FillSchema(dt, SchemaType.Source);
            objAdapter1.Fill(dt);

            dt.Columns.Add(ExcelConstants.LookupTypeHeader, typeof(String));
            dt.Columns.Add(ExcelConstants.RelationshipNameHeader, typeof(String));
            dt.Columns.Add(ExcelConstants.StatusHeader, typeof(String));
            return dt;
        }

        public static System.Data.DataTable SetAttributeNamePrefix(System.Data.DataTable sourceTable, string prefix, string oldPrefix)
        {

            System.Data.DataTable dt = sourceTable;
            if (!string.IsNullOrEmpty(prefix))
            {

                foreach (System.Data.DataRow row in dt.Rows)
                {
                    var attributeName = row[ExcelConstants.NameHeader];
                    if (attributeName != null)
                    {
                        string oldAttributeName = attributeName.ToString().ToLower();
                        //int prefixSeperatorIndex = oldAttributeName.IndexOf("_");

                        //string[] array = oldAttributeName.Split(new Char[] { '_' }, 1);
                        string newAttributeName = string.Empty;
                        if (!string.IsNullOrEmpty(oldAttributeName))
                        {
                            if (!string.IsNullOrEmpty(oldPrefix) && oldAttributeName.StartsWith(oldPrefix + "_"))
                            {
                                newAttributeName = oldAttributeName.Remove(0, (oldPrefix + "_").Length);
                            }
                            else
                            {
                                newAttributeName = oldAttributeName;
                            }
                        }
                        row[ExcelConstants.NameHeader] = prefix + "_" + newAttributeName;

                    }
                }
            }
            return dt;
        }

        public static void SaveAsXltm(string targetDirectory)
        {
            if (!string.IsNullOrEmpty(targetDirectory))
            {
                string currentDirectory = Directory.GetCurrentDirectory();
                string templatePath = currentDirectory + "\\BAC Import Template";
                string templateFile = "Bulk Atrribute Creator Import Template.xltm";
                Microsoft.Office.Interop.Excel.Application app = new Application();
                Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Open(templatePath + "\\" + templateFile);
                string targetFileName = targetDirectory + "\\Bulk Atrribute Creator Import File";
                if (File.Exists(targetFileName + ".xlsm"))
                {
                    string timeStamp = DateTime.Now.ToUniversalTime().ToBinary().ToString();
                    targetFileName = targetFileName + "-" + timeStamp + ".xlsm";

                }
                else
                {
                    targetFileName = targetFileName + ".xlsm";
                }
                workbook.SaveAs(targetFileName, XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
                workbook.Close(true, targetFileName, Type.Missing);
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(app);
                workbook = null;
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}
